﻿using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using Demo.Common.ExceptionHandlers;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Demo.DataAccess.DataAccessHandler
{
    public static class SqlHandler
    {
        private static readonly string _appConnectionString;

        static SqlHandler()
        {
            _appConnectionString = ConfigurationManager.AppSettings["DatabaseConnectionString"];
        }

        private static Database GetDatabase(string dbName)
        {
            try
            {
                return dbName == null ? DatabaseFactory.CreateDatabase() : DatabaseFactory.CreateDatabase(dbName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetDataTable(string spName)
        {
            try
            {
                Database db = GetDatabase(_appConnectionString);
                DbCommand cmd = db.GetStoredProcCommand(spName);
                DataSet ds = db.ExecuteDataSet(cmd);
                DataTable dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                bool rethrow = DataAccessExceptionHandler.HandleException(ref ex);
                if (rethrow)
                {
                    throw ex;
                }
                return null;
            }
        }

        public static DataTable GetDataTable(string spName, object[] parameters)
        {
            try
            {
                Database db = GetDatabase(_appConnectionString);
                DbCommand cmd = db.GetStoredProcCommand(spName, parameters);
                DataSet ds = db.ExecuteDataSet(cmd);
                DataTable dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                bool rethrow = DataAccessExceptionHandler.HandleException(ref ex);
                if (rethrow)
                {
                    throw ex;
                }
                return null;
            }
        }

        public static IDataReader GetDataReader(string spName)
        {
            try
            {
                Database db = GetDatabase(_appConnectionString);
                IDataReader reader = db.ExecuteReader(spName);
                return reader;
            }
            catch (Exception ex)
            {
                bool rethrow = DataAccessExceptionHandler.HandleException(ref ex);
                if (rethrow)
                {
                    throw ex;
                }
                return null;
            }
        }

        public static IDataReader GetDataReader(string spName, object[] parameters)
        {
            try
            {
                Database db = GetDatabase(_appConnectionString);
                IDataReader reader = db.ExecuteReader(spName, parameters);
                return reader;
            }
            catch (Exception ex)
            {
                bool rethrow = DataAccessExceptionHandler.HandleException(ref ex);
                if (rethrow)
                {
                    throw ex;
                }
                return null;
            }
        }

        public static object ExecuteNonQuery(string spName, object[] inParameters, bool outParameters)
        {
            try
            {
                Database db = GetDatabase(_appConnectionString);
                DbCommand cmd = db.GetStoredProcCommand(spName, inParameters);
                db.ExecuteNonQuery(cmd);
                return outParameters ? cmd.Parameters["Outparam"].Value : null;
            }
            catch (Exception ex)
            {
                bool rethrow = DataAccessExceptionHandler.HandleException(ref ex);
                if (rethrow)
                {
                    throw ex;
                }
                return null;
            }
        }
    }
}